"""
Case Type   : dolphin
Case Name   : 线程池关闭下，测试dolphin插件自动加载及创建与删除
Create At   : 2022.04.06
Owner       : z@wan005
Description :
    1、在默认A库下创建插件
    2、创建b库
    3、b库下查询dolphin插件
    4.1:兼容b库且有插件时验证mysql兼容语法
    4.2:兼容A库验证mysql兼容语法
    5、删除extension
    6、兼容b库再次创建同名插件
    7、兼容b库再次创建同名插件添加if not exists
    8、插件名错误
    9、清理环境
Expect      :
    1、合理报错
    2、成功
    3、自动加载成功,无需手动创建
    4.1、成功
    4.2、成功
    5、报错
    6、报错
    7、成功
    8、报错
    9、清理环境完成
History     :modified by z@wan005,2022.08.29，om数据库目前实现自动加载dolphin插件，
             因此删除用例中拷贝插件依赖文件步骤
             modified by zhanghuan96,2024.6.28，优化断言
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class PluginTest01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_plugin0001'
        self.log.info('查询线程池参数值')
        self.default_value = self.common.show_param('enable_thread_pool')
        sql_res = self.sh_primary.execut_db_sql('alter system set '
                                                'enable_thread_pool to off;')
        self.log.info(sql_res)
        self.sh_primary.restart_db_cluster()

    def test_dolphin(self):
        text = '----step1:在默认A库下创建插件;expect:合理报错----'
        self.log.info(text)
        sql_cmd = 'create extension dolphin;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn('ERROR:  extension "dolphin" is only supported '
                      'in B type databas', sql_res, '执行失败' + text)

        text = '----step2:创建b库;expect:成功----'
        self.log.info(text)
        sql_cmd = (f"set dolphin.b_compatibility_mode=off;"
                   f"drop database if exists {self.db_name};"
                   f"create database {self.db_name} DBCOMPATIBILITY ='B';")
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)

        text = '---step3:b库下查询dolphin插件;expect:自动加载成功,无需手动创建---'
        self.log.info(text)
        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual('dolphin', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step4.1:兼容b库且有插件时验证mysql兼容语法;expect:成功--'
        self.log.info(text)
        sql_cmd = "select regexp('a', true);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual('1', sql_res.splitlines()[2].strip(),
                         '执行失败' + text)

        text = '--step4.2:兼容A库验证mysql兼容语法;expect:报错--'
        self.log.info(text)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(
            'ERROR:  function regexp(unknown, boolean) does not exist',
            sql_res, '执行失败' + text)

        text = '---step5:删除extension;expect:报错---'
        self.log.info(text)
        sql_cmd = "drop extension dolphin;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ERROR', sql_res, '执行失败' + text)

        text = '--step6:兼容b库再次创建同名插件;expect:报错--'
        self.log.info(text)
        sql_cmd = 'create extension dolphin;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ERROR:  extension "dolphin" already exists in '
                      'schema "public"', sql_res, '执行失败' + text)

        text = '--step7:兼容b库再次创建同名插件添加if not exists;expect:成功--'
        self.log.info(text)
        sql_cmd = 'create extension if not exists dolphin;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn(self.constant.create_extension_success, sql_res,
                      '执行失败' + text)

        text = '--step8:插件名错误;expect:报错--'
        self.log.info(text)
        sql_cmd = 'create extension a_sql_plugin;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('ERROR:  could not open extension control file',
                      sql_res)

    def tearDown(self):
        text = '--step9:清理环境;expect:清理环境完成--'
        self.log.info(text)
        sql_cmd = self.sh_primary.execut_db_sql(f'alter system set '
                                                f'enable_thread_pool to '
                                                f'{self.default_value};')
        self.log.info(sql_cmd)
        self.sh_primary.restart_db_cluster()
        status = self.sh_primary.get_db_cluster_status()
        self.log.info(status)
        drop_cmd = (f"set dolphin.b_compatibility_mode=off;"
                    f"drop database if exists {self.db_name};")
        drop_res = self.sh_primary.execut_db_sql(drop_cmd)
        self.log.info(drop_res)
        self.log.info('断言teardown成功')
        self.assertIn(self.constant.ALTER_SYSTEM_SUCCESS_MSG, sql_cmd,
                      '执行失败' + text)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败' + text)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
